﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Text;

namespace WebApplication1.Controller
{
    public class Class1
    {
        private OdbcConnection MyConnection;
        private OdbcCommand MyCommand;
        private OdbcDataReader MyDataReader;
        public void ketnoi()
        {
            
            string MyConString = "DRIVER={MySQL ODBC 5.1 Driver};" +
                           "SERVER=localhost;" +
                           "PORT=3308;" +
                           "DATABASE=vinhtuong;" +
                           "UID=root;" +
                           "PASSWORD=1234;" +
                           "OPTION=0;Character Set=utf8";
            MyConnection = new OdbcConnection(MyConString);
            MyConnection.Open();
        }
        public void updatepatient(patient items)
        {
            ketnoi();
            //byte[] utfbyte = Encoding.Convert(Encoding.Unicode, Encoding.UTF8, Encoding.Unicode.GetBytes(items.PatientName));
            //items.PatientName = Encoding.Default.GetString(utfbyte);
            string query = "UPDATE patient SET PatientName=N'"+items.PatientName+"' WHERE PatientCode='"+items.PatientCode+"' ";
            MyCommand = new OdbcCommand();
            //Assign the query using CommandText
            MyCommand.CommandText = query;
            //Assign the connection using Connection
            MyCommand.Connection = MyConnection;

            //Execute query
            MyCommand.ExecuteNonQuery();
            dongketnoi();
        }
        public void insertItems(patient items)
        {
            ketnoi();
            string st = items.Birthday.Date.ToString("yyyy-MM-dd HH:MM:ss");
            string st1 = items.NgayCapCMND.Date.ToString("yyyy-MM-dd HH:MM:ss");
            string query = "INSERT INTO patient(PatientCode,PatientName,Birthday,GioiTinhCode,NgheNghiepCode,HC_DanTocCode,HC_QuocGiaCode,HC_SoNha,HC_Thon,HC_XaCode,HC_HuyenCode,HC_TinhCode,NoiLamViec,NguoiThan,NguoiThan_Phone,CMND,NoiCapCMND,NgayCapCMND,PatientRemark) Values" +
                "(N'" + items.PatientCode + "',N'" + items.PatientName + "',N'" + st + "',N'" + items.GioiTinhCode + "',N'" + items.NgheNghiepCode + "',N'" + items.HC_DanTocCode + "',N'" + items.HC_QuocGiaCode + "',N'" + items.HC_SoNha + "',N'" + items.HC_Thon + "',N'" + items.HC_XaCode + "',N'" + items.HC_HuyenCode + "',N'" + items.HC_TinhCode + "',N'" + items.NoiLamViec + "',N'" + items.NguoiThan + "',N'" + items.NguoiThan_Phone + "',N'" + items.CMND + "',N'" + items.NoiCapCMND + "',N'" + st1 + "',N'" + items.PatientRemark + "')";
            MyCommand = new OdbcCommand();
            MyCommand.CommandText = query;
            //Assign the connection using Connection
            MyCommand.Connection = MyConnection;

            //Execute query
            MyCommand.ExecuteNonQuery();
            dongketnoi();
        }
        public patient SelectPatient(string st)
        {
            ketnoi();
                    patient lst =new patient();
                    MyCommand = new OdbcCommand();
                    MyCommand.Connection = MyConnection;
                    MyCommand.CommandText = st;
                    MyDataReader = MyCommand.ExecuteReader();
                    while (MyDataReader.Read())
                    {
                        lst.PatientID =Convert.ToInt32(MyDataReader["PatientID"]); 
                        lst.PatientCode = MyDataReader["PatientCode"].ToString();
                       // lst.PatientName = Encoding.UTF8.GetString(Encoding.Default.GetBytes(lst.PatientName = MyDataReader["PatientName"].ToString()));
                        lst.PatientName = MyDataReader["PatientName"].ToString();
                        try
                        {
                            lst.Birthday = Convert.ToDateTime(MyDataReader["Birthday"]);
                        }
                        catch { ;}
                        lst.GioiTinhCode = MyDataReader["GioiTinhCode"].ToString();
                        lst.NgheNghiepCode = MyDataReader["NgheNghiepCode"].ToString();
                        lst.HC_DanTocCode = MyDataReader["HC_DanTocCode"].ToString();
                        lst.HC_QuocGiaCode = MyDataReader["HC_QuocGiaCode"].ToString();
                        lst.HC_SoNha = MyDataReader["HC_SoNha"].ToString();
                        lst.HC_Thon = MyDataReader["HC_Thon"].ToString();
                        lst.HC_XaCode = MyDataReader["HC_XaCode"].ToString();
                        lst.HC_HuyenCode = MyDataReader["HC_HuyenCode"].ToString();
                        lst.HC_TinhCode = MyDataReader["HC_TinhCode"].ToString();
                        lst.NoiLamViec = MyDataReader["NoiLamViec"].ToString();
                        lst.NguoiThan = MyDataReader["NguoiThan"].ToString();
                        lst.NguoiThan_Phone = MyDataReader["NguoiThan_Phone"].ToString();
                        lst.Bo_Name = MyDataReader["Bo_Name"].ToString();
                        lst.Bo_TrinhDoVanHoaID = MyDataReader["Bo_TrinhDoVanHoaID"].ToString();
                        lst.Bo_NgheNghiepCode = MyDataReader["Bo_NgheNghiepCode"].ToString();
                        lst.Me_Name = MyDataReader["Me_Name"].ToString();
                        lst.Me_TrinhDoVanHoaID = MyDataReader["Me_TrinhDoVanHoaID"].ToString();
                        lst.Me_NgheNghiepCode = MyDataReader["Me_NgheNghiepCode"].ToString();
                        lst.CMND = MyDataReader["CMND"].ToString();
                        lst.NoiCapCMND = MyDataReader["NoiCapCMND"].ToString();
                        lst.RegisterDate = MyDataReader["RegisterDate"].ToString();
                        try
                        {
                            lst.NgayCapCMND = Convert.ToDateTime(MyDataReader["NgayCapCMND"]);
                        }
                        catch { ;}
                        lst.NguoiGioiThieuID = MyDataReader["NguoiGioiThieuID"].ToString();
                        lst.PatientRemark = MyDataReader["PatientRemark"].ToString();
                    }
                    dongketnoi();
                    return lst;
        }
        public void insertPatient(patient items)
        {
            
        }

        public void dongketnoi()
        {
            MyConnection.Dispose();
            MyConnection.Close();
        }
       public class patient
       {
           public Int32 PatientID { get; set; }
        public String PatientName{get;set;}
        public String PatientCode { get; set; }
        public DateTime Birthday { get; set; }
        public String GioiTinhCode { get; set; }
        public String NgheNghiepCode { get; set; }
        public String HC_DanTocCode { get; set; }
        public String HC_QuocGiaCode { get; set; }
        public String HC_SoNha { get; set; }
        public String HC_Thon { get; set; }
        public String HC_XaCode { get; set; }
        public String HC_HuyenCode { get; set; }
        public String HC_TinhCode { get; set; }
        public String NoiLamViec { get; set; }
        public String NguoiThan { get; set; }
        public String NguoiThan_Phone { get; set; }
        public String Bo_Name { get; set; }
        public String Bo_TrinhDoVanHoaID { get; set; }
        public String Bo_NgheNghiepCode { get; set; }
        public String Me_Name { get; set; }
        public String Me_TrinhDoVanHoaID { get; set; }
        public String Me_NgheNghiepCode { get; set; }
        public String CMND { get; set; }
        public String NoiCapCMND { get; set; }
        public DateTime NgayCapCMND { get; set; }
        public String RegisterDate { get; set; }
        public String LastAccessDate { get; set; }
        public String NguoiGioiThieuID { get; set; }
        public String PatientRemark { get; set; }

       }
    }
}